package com.simpleaccount.Db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.simpleaccount.Entity.Category;
import com.simpleaccount.Entity.Entry;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Administrator on 2017/7/13.
 */

public class DbUtils {
    private static SQLiteDatabase db;
    private static DatabaseHelper dbh;

    public static void init(Context context){
        dbh = new DatabaseHelper(context,"SimpleAccounting",null,2);
        db = dbh.getWritableDatabase();
    }
    public static void init(SQLiteDatabase db1){
        db=db1;
    }

    public static void insertEntry(Entry entry){
        ContentValues cv = new ContentValues();
        if (entry.getObjectId()!=null){
            cv.put("objectId",entry.getObjectId());
        }
        cv.put("money",entry.getMoney());
        cv.put("category",entry.getCategory());
        cv.put("msg",entry.getMsg());
        cv.put("date", entry.getDate().getTime()/1000);
        cv.put("color",entry.getColor());
        cv.put("type",entry.getType());
        db.replace("Entry",null,cv);
    }
    public static void insertCategory(String name,int color,int type){
        ContentValues cv = new ContentValues();
        cv.put("name",name);
        cv.put("color",color);
        cv.put("type",type);
        db.replace("category",null,cv);
    }

    public static void modifyEntry(Entry entry){
        ContentValues cv = new ContentValues();
        if(entry.getObjectId()!=null){
            cv.put("objectId",entry.getObjectId());
        }
        cv.put("money",entry.getMoney());
        cv.put("category",entry.getCategory());
        cv.put("msg",entry.getMsg());
        cv.put("date", entry.getDate().getTime()/1000);
        cv.put("color",entry.getColor());
        String whereClause = "id=?";//修改条件
        String[] whereArgs = {String.valueOf(entry.getId())};//修改条件的参数
        db.update("Entry",cv,whereClause,whereArgs);
    }
    public static void deleteEntry(int id){
        String whereClause = "id=?";//删除的条件
        String[] whereArgs = {String.valueOf(id)};//删除的条件参数
        db.delete("Entry",whereClause,whereArgs);//执行删除
    }
    public static void deleteUploadedEntry(){
        String whereClause = "objectId IS NOT NULL ";//删除的条件
        String[] whereArgs = null;//删除的条件参数
        db.delete("Entry",whereClause,whereArgs);//执行删除
    }
    public static List<Entry> queryByDate(Date datefrom,Date dateTo){
        String df = String.valueOf(datefrom.getTime()/1000);
        String dt = String.valueOf(dateTo.getTime()/1000);
        List<Entry> entries= new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from entry where date > ? and date < ? order by date desc",new String[]{df,dt});
        while (cursor.moveToNext()){
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date")) * 1000);
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            Entry entry = new Entry(id, money, cate, date, msg,color,type);
            entries.add(entry);
        }
        return entries;
    }
    public static List<Entry> queryByCategoryAndDate(String category,Date datefrom,Date dateTo){
        List<Entry> entries = new ArrayList<>();
        String df = String.valueOf(datefrom.getTime()/1000);
        String dt = String.valueOf(dateTo.getTime()/1000);
        Cursor cursor = db.rawQuery("select * from entry where category = ? and date > ? and date < ? order by date desc",new String[]{category,df,dt});
        while (cursor.moveToNext()){
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date")) * 1000);
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            Entry entry = new Entry(id, money, cate, date, msg,color,type);
            entries.add(entry);
        }
        return entries;
    }
    public static List<Entry> queryByCategory(String category){
        List<Entry> entries = new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from entry where category = ? order by date desc",new String[]{category});
        while (cursor.moveToNext()){
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date")) * 1000);
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            Entry entry = new Entry(id, money, cate, date, msg,color,type);
            entries.add(entry);
        }
        return entries;
    }
    public static List<Entry> getEntries(){
        List<Entry> entries = new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from entry order by date desc",null);
        while (cursor.moveToNext()){
            String objectId = cursor.getString(cursor.getColumnIndex("objectId"));
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date")) * 1000);
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            Entry entry = new Entry(id, money, cate, date, msg,color,type);
            if(objectId!=null){
                entry.setObjectId(objectId);
            }
            entries.add(entry);
        }
        return entries;
    }
    public static List<Entry> getEntry(int sid,int num){
        List<Entry> entries = new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from entry where id > ? order by date desc limit ?",new String[]{String.valueOf(sid),String.valueOf(num)});
        while (cursor.moveToNext()){
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date")) * 1000);
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            Entry entry = new Entry(id, money, cate, date, msg,color,type);
            entries.add(entry);
        }
        return entries;
    }
    public static List<Entry> getEntry(int num){
        List<Entry> entries = new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from entry order by date desc limit ?",new String[]{String.valueOf(num)});
        while (cursor.moveToNext()){
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date")) * 1000);
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            Entry entry = new Entry(id, money, cate, date, msg,color,type);
            entries.add(entry);
        }
        return entries;
    }
    public static Entry getEntryById(int id){
        Entry entry=null;
        Cursor cursor = db.rawQuery("select * from entry where id = ?",new String[]{String.valueOf(id)});
        if(cursor.moveToFirst()){
            String cate = cursor.getString(cursor.getColumnIndex("category"));
            double money = cursor.getDouble(cursor.getColumnIndex("money"));
            String msg = cursor.getString(cursor.getColumnIndex("msg"));
            Date date = new Date(cursor.getLong(cursor.getColumnIndex("date"))*1000);
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            int type = cursor.getInt(cursor.getColumnIndex("type"));
            entry = new Entry(id, money, cate, date, msg,color,type);
        }
        return entry;
    }
public static List<Category> getCategory(){
    List<Category> categories = new ArrayList<>();
    Cursor cursor = db.rawQuery("select * from category",null);
    while (cursor.moveToNext()){
        String name =cursor.getString(cursor.getColumnIndex("name"));
        int color =cursor.getInt(cursor.getColumnIndex("color"));
        int type =cursor.getInt(cursor.getColumnIndex("type"));
        Category cate = new Category(name,color,type);
        categories.add(cate);
    }
    return categories;
}
    public static List<Category> getCategory(int type){
        List<Category> categories = new ArrayList<>();
        Cursor cursor = db.rawQuery("select * from category where type = ?",new String[]{String.valueOf(type)});
        while (cursor.moveToNext()){
            String name =cursor.getString(cursor.getColumnIndex("name"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            Category cate = new Category(name,color,type);
            categories.add(cate);
        }
        return categories;
    }
    public static Category getCategory(String name){
        Category cate =null;
        Cursor cursor = db.rawQuery("select * from category where name = ?",new String[]{name});
        if (cursor.moveToNext()){
            int type =cursor.getInt(cursor.getColumnIndex("type"));
            int color =cursor.getInt(cursor.getColumnIndex("color"));
            cate = new Category(name,color,type);
        }
        return cate;
    }
    public static int getCount(){
        int cnt =-1;
        Cursor cursor = db.rawQuery("select count(*) as cnt from entry",null);
        if(cursor.moveToFirst()){
            cnt=cursor.getInt(cursor.getColumnIndex("cnt"));
        }
        return cnt;
    }
    public static void deleteAllEntries(){
        db.delete("Entry",null,null);//执行删除
    }
}
